package com.b511.swpu.cementbackend;


import com.b511.swpu.cementbackend.entity.*;
import com.b511.swpu.cementbackend.repository.*;
//import org.apache.poi.xssf.usermodel.XSSFRow;
//import org.apache.poi.xssf.usermodel.XSSFSheet;
//import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.junit.runner.RunWith;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.core.io.ClassPathResource;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.ResourceUtils;


import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest
public class InitSysModuleData {

    @Autowired
    Sys_ModuleRepository sys_moduleRepository;

    @Autowired
    Sys_Module_DataRepository sys_module_dataRepository;

    @Autowired
    CM_WK_AdditiveConsumeRepository additiveConsumeRepository;

    @Autowired
    CM_WK_AccessoryConsumeRepository accessoryConsumeRepository;

    @Autowired
    BA_WellRepository baWellRepository;

    @Autowired
    CM_WK_WorkRecordRepository workRecordRepository;

    @Autowired
    CM_WK_MudFormulaRepository mudFormulaRepository;

    @Autowired
    CM_WK_MudPropertyRepository mudPropertyRepository;

    @Autowired
    CM_WorkBasicRepository workBasicRepository;

//    @Test
//    // @Transactional
//    public void importModule(){
//        // excel中的sheet
//        XSSFSheet sheet;
//        try {
//            // 位置：resources/Data.xlsx
//            File file =  ResourceUtils.getFile("classpath:SYS_MODULE.xlsx");
//            XSSFWorkbook sheets = new XSSFWorkbook(file);
//            //获取sheet
//            sheet = sheets.getSheet("MODULE");
//
//            //获取行数
//            int rows = sheet.getPhysicalNumberOfRows();
//
//            List<Sys_Module> moduleList = new ArrayList<>();
//            // 遍历行
//            for(int i=1;i<rows && sheet.getRow(i)!=null;i++){
//                XSSFRow row = sheet.getRow(i);
//                // 对行数据进行处理
//                if(row.getCell(0)!=null){
//                    Sys_Module tmpModule = new Sys_Module();
//                    // 插入到SysModule
//                    tmpModule.setModuleName(row.getCell(0).toString());
//                    moduleList.add(tmpModule);
//                }
//            }
//            sys_moduleRepository.saveAll(moduleList);
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//    }
//
//    @Test
//    // @Transactional
//    public void importModuleData(){
//        // excel中的sheet
//        XSSFSheet sheet;
//        try {
//            // 位置：resources/Data.xlsx
//            File file =  ResourceUtils.getFile("classpath:SYS_MODULE_DATA.xlsx");
//            XSSFWorkbook sheets = new XSSFWorkbook(file);
//            //获取sheet
//            sheet = sheets.getSheet("moduleData");
//
//            //获取行数
//            int rows = sheet.getPhysicalNumberOfRows();
//
//            List<Sys_Module_Data> moduleDataList = new ArrayList<>();
//
//            // 遍历行
//            for(int i=1;i<rows && sheet.getRow(i)!=null;i++){
//                XSSFRow row = sheet.getRow(i);
//                // 对行数据进行处理
//                if(row.getCell(0)!=null){
//                    Sys_Module_Data module_data = new Sys_Module_Data();
//                    Sys_Module tmpModule = sys_moduleRepository.findOneByModuleName(row.getCell(0).toString());
//                    module_data.setModuleName(row.getCell(1).toString());
//                    module_data.setFieldName(row.getCell(2).toString());
//                    module_data.setTableName(row.getCell(3).toString());
//                    module_data.setLabel(row.getCell(4).toString());
//                    module_data.setModule(tmpModule);
//                    moduleDataList.add(module_data);
//                }
//            }
//            sys_module_dataRepository.saveAll(moduleDataList);
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//    }

    // 12.16 导入作业数据表的数据到 SYS_MODULE 以及 SYS_MODULE_DATA
    @Test
    public void importModuleData(){
        // TODO: 关联查询失败，问题在于是通过 BA_WELL_ID 进行关联，这几张表，BA_WELL_ID为空 或者 没有这个字段
        // excel中的sheet
        XSSFSheet sheet;
        try {
            // 位置：resources/Data.xlsx
            File file =  ResourceUtils.getFile("classpath:MODULE_DATA.xlsx");
            XSSFWorkbook sheets = new XSSFWorkbook(file);
            //获取sheet
            sheet = sheets.getSheet("Sheet1");

            //获取行数
            int rows = sheet.getPhysicalNumberOfRows();

            List<Sys_Module_Data> moduleDataList = new ArrayList<>();

            // 遍历行
            for(int i=1;i<rows && sheet.getRow(i)!=null;i++){
                XSSFRow row = sheet.getRow(i);
                // 对行数据进行处理
                if(row.getCell(0)!=null){
                    Sys_Module_Data module_data = new Sys_Module_Data();
                    Sys_Module tmpModule = sys_moduleRepository.findOneByModuleName(row.getCell(0).toString());
                    module_data.setModuleName(row.getCell(1).toString());
                    module_data.setTableName(row.getCell(2).toString());
                    module_data.setFieldName(row.getCell(3).toString());
                    module_data.setLabel(row.getCell(4).toString());
                    module_data.setModule(tmpModule);
                    moduleDataList.add(module_data);
                }
            }
            sys_module_dataRepository.saveAll(moduleDataList);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 插入 BA_WELL_ID 到 CM_WK_ADDITIVE_CONSUME 表
    @Test
    public void updateAdditiveConsume(){
        // Map<String, Long> wellName: ba_well_id
        Map<String, Long> wellMap = new HashMap<String, Long>();
        // 全部的井
        List<BA_Well> wellList = baWellRepository.findAll();
        for(BA_Well well : wellList){
            wellMap.put(well.getWellName(), well.getBA_WELL_ID());
        }

        List<CM_WK_AdditiveConsume> additiveConsumes = additiveConsumeRepository.findAll();
        for (CM_WK_AdditiveConsume tmpAdditive : additiveConsumes){
            String wellName = tmpAdditive.getWellName();
            if (wellMap.containsKey(wellName)){
                tmpAdditive.setBaWellId(wellMap.get(wellName).toString());
            }
        }
        additiveConsumeRepository.saveAll(additiveConsumes);
    }

    // 插入 BA_WELL_ID 到 CM_WK_ACCESSORY_CONSUME 表
    @Test
    public void updateAccessoryConsume(){
        // Map<String, Long> wellName: ba_well_id
        Map<String, Long> wellMap = new HashMap<String, Long>();
        // 全部的井
        List<BA_Well> wellList = baWellRepository.findAll();
        for(BA_Well well : wellList){
            wellMap.put(well.getWellName(), well.getBA_WELL_ID());
        }

        List<CM_WK_AccessoryConsume> accessoryConsumes = accessoryConsumeRepository.findAll();
        for (CM_WK_AccessoryConsume tmpAccessory : accessoryConsumes){
            String wellName = tmpAccessory.getWellName();
            if (wellMap.containsKey(wellName)){
                tmpAccessory.setBaWellId(wellMap.get(wellName).toString());
            }
        }
        accessoryConsumeRepository.saveAll(accessoryConsumes);
    }

    // 插入 BA_WELL_ID 到 CM_WK_WORK_RECORD 表
    @Test
    //@Transactional
    public void updateWorkRecord(){
        // Map<String, Long> wellName: ba_well_id
        Map<String, Long> wellMap = new HashMap<String, Long>();
        // 全部的井
        List<BA_Well> wellList = baWellRepository.findAll();
        for(BA_Well well : wellList){
            wellMap.put(well.getWellName(), well.getBA_WELL_ID());
        }

        List<CM_WK_WorkRecord> workRecordList = workRecordRepository.findAll();
        for (CM_WK_WorkRecord tmpWorkRecord : workRecordList){
            String wellName = tmpWorkRecord.getWellName();
            if (wellMap.containsKey(wellName)){
                tmpWorkRecord.setBaWellId(wellMap.get(wellName).toString());
            }
        }
        workRecordRepository.saveAll(workRecordList);
    }

    // 插入 BA_WELL_ID 到 CM_WK_MUD_FORMULA 表
    @Test
    // @Transactional
    public void updateMudFormula(){
        // Map<String, Long> wellName: ba_well_id
        Map<String, Long> wellMap = new HashMap<String, Long>();
        // 全部的井
        List<BA_Well> wellList = baWellRepository.findAll();
        for(BA_Well well : wellList){
            wellMap.put(well.getWellName(), well.getBA_WELL_ID());
        }

        List<CM_WK_MudFormula> mudFormulaList = mudFormulaRepository.findAll();
        for (CM_WK_MudFormula tmpFormula : mudFormulaList){
            String wellName = tmpFormula.getWellName();
            if (wellMap.containsKey(wellName)){
                tmpFormula.setBaWellId(wellMap.get(wellName).toString());
            }
        }
        mudFormulaRepository.saveAll(mudFormulaList);
    }

    // 插入 BA_WELL_ID 到 CM_WK_MUD_PROPERTY表
    @Test
    // @Transactional
    public void updateMudProperty(){
        // Map<String, Long> wellName: ba_well_id
        Map<String, Long> wellMap = new HashMap<String, Long>();
        // 全部的井
        List<BA_Well> wellList = baWellRepository.findAll();
        for(BA_Well well : wellList){
            wellMap.put(well.getWellName(), well.getBA_WELL_ID());
        }

        List<CM_WK_MudProperty> mudPropertyList = mudPropertyRepository.findAll();
        for (CM_WK_MudProperty tmpMudProperty : mudPropertyList){
            String wellName = tmpMudProperty.getWellName();
            if (wellMap.containsKey(wellName)){
                tmpMudProperty.setBaWellId(wellMap.get(wellName).toString());
            }
        }
        mudPropertyRepository.saveAll(mudPropertyList);
    }

    // 插入 BA_WELL_ID 到 CM_WK_BASIC 表
    @Test
    // @Transactional
    public void updateBasic(){
        // Map<String, Long> wellName: ba_well_id
        Map<String, Long> wellMap = new HashMap<String, Long>();
        // 全部的井
        List<BA_Well> wellList = baWellRepository.findAll();
        for(BA_Well well : wellList){
            wellMap.put(well.getWellName(), well.getBA_WELL_ID());
        }

        List<CM_WK_BASIC> workBasicList = workBasicRepository.findAll();
        for (CM_WK_BASIC tmpBasic : workBasicList){
            String wellName = tmpBasic.getWellName();
            if (wellMap.containsKey(wellName)){
                tmpBasic.setBA_WELL_ID(wellMap.get(wellName).toString());
            }
        }
        workBasicRepository.saveAll(workBasicList);
    }


}
